import os

import mysql.connector

conn = mysql.connector.connect(
    host="127.0.0.1",
    user="root",
    password="123456",
    database="test",
    allow_local_infile=True
)
cursor = conn.cursor()

# ---------------- 配置 ----------------
folder_path = r"F:\AData\daily20250829\daily"   # CSV 文件夹路径
out_path = r"F:\\AData\\daily20250829"
table_name = "stock_daily"               # 目标表名
fields = ["ts_code", "trade_date", "open", "high", "low", "close",
          "pre_close", "change", "pct_chg", "vol", "amount"]  # 表字段
ignore_header = True                     # 是否忽略 CSV 第一行标题

output_sql_file = os.path.join(out_path, "batch_load_data.sql")  # 输出 SQL 文件
# --------------------------------------

sql_statements = []

i  = 0
for filename in os.listdir(folder_path):
    if filename.lower().endswith(".csv"):
        full_path = os.path.join(folder_path, filename).replace("\\", "\\\\")  # MySQL 要求路径用 /
        # 确保路径格式正确，Windows下可能需要转义
        # print(full_path)
        # exit()
        sql = f"LOAD DATA LOCAL INFILE '{full_path}'\n"
        sql += f"INTO TABLE {table_name}\n"
        sql += "FIELDS TERMINATED BY ','\n"
        sql += "LINES TERMINATED BY '\\n'\n"
        if ignore_header:
            sql += "IGNORE 1 LINES\n"
        sql += "(" + ", ".join([f"`{f}`" for f in fields]) + ");\n"
        i += 1
        # print(sql)
        cursor.execute(sql)
        
        if i % 100 == 0:
            conn.commit()
            print(f"已插入 {i} 条数据")
        # sql_statements.append(sql)

# # 写入 SQL 文件
# with open(output_sql_file, "w", encoding="utf-8") as f:
#     f.writelines(sql_statements)

# print(f"生成 SQL 文件: {output_sql_file}")
if i % 100 != 0:
    conn.commit()
    print(f"已插入 {i} 条数据")
cursor.close()
conn.close()
print(f"共生成 {len(sql_statements)} 条 LOAD DATA SQL 语句")
